#!/usr/bin/env python
# -*- encoding: utf-8 -*-
# Project: pymysql3-2

import pymysql
import json


def extDictFromJson(jsonStr):
    dic = json.loads(jsonStr)
    return dic


def insertTarget(tgCursor, sql):
    try:
        tgCursor.execute(sql)
        # tgDb.commit()
    except Exception as e:
        # tgDb.rollback()
        print("Error: unable to fecth data:" + str(e))


db = pymysql.connect(host="localhost",
                     user="root",
                     password="password",
                     port=13306,  # 端口
                     database="resultdb",
                     charset='utf8')

cursor = db.cursor()


tgDb = pymysql.connect(host="localhost",
                       user="mall",
                       password="mall",
                       port=3306,
                       database="mall",
                       charset='utf8')

tgCursor = tgDb.cursor()

# sql = "SELECT result FROM bxHomePageBelowConten WHERE taskid = '{}'".format('0051055424271aaf255e1de14115bed8')
sql = "SELECT result FROM bxHomePageBelowConten"
try:
    # 执行SQL语句
    cursor.execute(sql)
    # 获取所有记录列表
    results = cursor.fetchall()
    for row in results:
        for result in row:
            # result = row[0]
            resultStr = bytes(result).decode('utf-8')
            resDic = extDictFromJson(resultStr)
            # 打印结果
            # print("result={}".format(resultStr))
            # print()
            # print("---------------- 商品信息 --------------")
            # print("goodsInfo.goodsId={}".format(resDic['goodsInfo']['goodsId']))
            # print("goodsInfo.name={}".format(resDic['goodsInfo']['name']))
            # print("goodsInfo.image={}".format(resDic['goodsInfo']['image']))
            # print("goodsInfo.mallPrice={}".format(resDic['goodsInfo']['mallPrice']))
            # print("goodsInfo.price={}".format(resDic['goodsInfo']['price']))
            # # print("---------------- 图片信息 --------------")
            # print("img.img_url={}".format(resDic['img_url']))
            # print("img.file_name={}".format(resDic['file_name']))
            # print("img.file_path={}".format(resDic['file_path']))

            goodsInfoSQL = "insert into goods_info values ('{}','{}','{}',{},{},'{}','{}','{}')"\
                .format(resDic['goodsInfo']['goodsId'],
                        resDic['goodsInfo']['name'],
                        resDic['goodsInfo']['image'],
                        resDic['goodsInfo']['mallPrice'],
                        resDic['goodsInfo']['price'],
                        resDic['img_url'],
                        resDic['file_name'],
                        resDic['file_path'])
            print(goodsInfoSQL)
            # tgCursor.execute(goodsInfoSQL)
            insertTarget(tgCursor, goodsInfoSQL)
            print()

            # print("---------------- 商品营销广告信息 --------------")
            # print("goodsInfo.goodsId={}".format(resDic['goodsInfo']['goodsId']))
            # print("advPicAddr={}".format(resDic['getGoodDetailById']['data']['advertesPicture']['PICTURE_ADDRESS']))
            # print("advPicPlace={}".format(resDic['getGoodDetailById']['data']['advertesPicture']['TO_PLACE']))
            # print("advPicUrlType={}".format(resDic['getGoodDetailById']['data']['advertesPicture']['urlType']))

            advSQL = "insert into goods_sale_adv values ('{}','{}',{},{})"\
                .format(resDic['goodsInfo']['goodsId'],
                        resDic['getGoodDetailById']['data']['advertesPicture']['PICTURE_ADDRESS'],
                        resDic['getGoodDetailById']['data']['advertesPicture']['TO_PLACE'],
                        resDic['getGoodDetailById']['data']['advertesPicture']['urlType']
                        )
            print(advSQL)
            # tgCursor.execute(advSQL)
            insertTarget(tgCursor, advSQL)
            print()

            # print("---------------- 商品详情信息 --------------")
            # print("goodsId={}".format(resDic['getGoodDetailById']['data']['goodInfo']['goodsId']))
            # print("goodsName={}".format(resDic['getGoodDetailById']['data']['goodInfo']['goodsName']))
            # print("shopId={}".format(resDic['getGoodDetailById']['data']['goodInfo']['shopId']))
            # print("isOnline={}".format(resDic['getGoodDetailById']['data']['goodInfo']['isOnline']))
            # print("state={}".format(resDic['getGoodDetailById']['data']['goodInfo']['state']))
            # print("amount={}".format(resDic['getGoodDetailById']['data']['goodInfo']['amount']))
            # print("TYPE_SHIP={}".format(resDic['getGoodDetailById']['data']['goodInfo']['TYPE_SHIP']))
            # print("goodsSerialNumber={}".format(resDic['getGoodDetailById']['data']['goodInfo']['goodsSerialNumber']))
            # print("oriPrice={}".format(resDic['getGoodDetailById']['data']['goodInfo']['oriPrice']))
            # print("presentPrice={}".format(resDic['getGoodDetailById']['data']['goodInfo']['presentPrice']))
            # # 商品图片
            # print("comPic={}".format(resDic['getGoodDetailById']['data']['goodInfo']['comPic']))
            # print("image1={}".format(resDic['getGoodDetailById']['data']['goodInfo']['image1']))
            # print("image2={}".format(resDic['getGoodDetailById']['data']['goodInfo']['image2']))
            # print("image3={}".format(resDic['getGoodDetailById']['data']['goodInfo']['image3']))
            # print("image4={}".format(resDic['getGoodDetailById']['data']['goodInfo']['image4']))
            # print("image5={}".format(resDic['getGoodDetailById']['data']['goodInfo']['image5']))
            # # 商品详情HTML
            # print("goodsDetail={}".format(resDic['getGoodDetailById']['data']['goodInfo']['goodsDetail']))
            goodsDetailSQL = "insert into goods_detl_info values ('{}','{}','{}','{}',{},{},{},'{}',{},{},'{}','{}','{}','{}','{}','{}','{}')"\
                .format(resDic['getGoodDetailById']['data']['goodInfo']['goodsId'],
                        resDic['getGoodDetailById']['data']['goodInfo']['goodsName'],
                        resDic['getGoodDetailById']['data']['goodInfo']['shopId'],
                        resDic['getGoodDetailById']['data']['goodInfo']['isOnline'],
                        resDic['getGoodDetailById']['data']['goodInfo']['state'],
                        resDic['getGoodDetailById']['data']['goodInfo']['amount'],
                        resDic['getGoodDetailById']['data']['goodInfo']['TYPE_SHIP'],
                        resDic['getGoodDetailById']['data']['goodInfo']['goodsSerialNumber'],
                        resDic['getGoodDetailById']['data']['goodInfo']['oriPrice'],
                        resDic['getGoodDetailById']['data']['goodInfo']['presentPrice'],
                        resDic['getGoodDetailById']['data']['goodInfo']['comPic'],
                        resDic['getGoodDetailById']['data']['goodInfo']['image1'],
                        resDic['getGoodDetailById']['data']['goodInfo']['image2'],
                        resDic['getGoodDetailById']['data']['goodInfo']['image3'],
                        resDic['getGoodDetailById']['data']['goodInfo']['image4'],
                        resDic['getGoodDetailById']['data']['goodInfo']['image5'],
                        resDic['getGoodDetailById']['data']['goodInfo']['goodsDetail']
                        )
            print(goodsDetailSQL)
            # tgCursor.execute(goodsDetailSQL)
            insertTarget(tgCursor, goodsDetailSQL)
            tgDb.commit()
except Exception as e:
    tgDb.rollback()
    print("Error: unable to fecth data:" + str(e))

tgCursor.close()
tgDb.close()
cursor.close()
db.close()
